package co.yixiang.modules.shop.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * IO流工具类
 *
 * @author liufeidong
 */
@SuppressWarnings("static-access")
public class IOUtil {

    private IOUtil() {
    }

    private static IOUtil ioUtil = null;

    public static synchronized IOUtil getIOUtil() {
        if (ioUtil == null) {
            ioUtil = new IOUtil();
        }

        return ioUtil;
    }

    /**
     * 导入xlsx
     *
     * @param in 导入的xlsx读取流
     * @return list
     * @author liufeidong
     */
    public static List<List<String>> importXlsx(InputStream in) throws Exception {
        List<List<String>> list = new ArrayList<List<String>>();
        XSSFWorkbook book = null;
        book = new XSSFWorkbook(in);

        // 循环xlsx每一页
        for (XSSFSheet sheet : book) {
            if (sheet == null) {
                continue;
            }
            XSSFRow headRow = sheet.getRow(0);// 表头
            if (headRow == null) {
                continue;
            }
            int minColIx = headRow.getFirstCellNum();// 获取第一列序号
            int maxColIx = headRow.getLastCellNum();// 获取最后一列序号
            // 循环当页的每一行
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {// 从下标“1”开始，跳过表头
                XSSFRow row = sheet.getRow(rowNum);// 获取当前行
                if (row == null) {
                    continue;
                }

                List<String> rowList = new ArrayList<String>();// 定义rowList

                // 循环每行的每一列
                for (int colIx = minColIx; colIx < maxColIx; colIx++) {
                    XSSFCell cell = row.getCell(colIx);// 获取当前列

                    if (cell == null) {
                        rowList.add("");// 将当前行的每一列保存到rowList
                    } else {
                        if (cell.getCellType() == cell.CELL_TYPE_NUMERIC && HSSFDateUtil.isCellDateFormatted(cell)) {// 日期类型，转格式
                            rowList.add(DateUtil.formatStrByDate(cell.getDateCellValue(), "yyyy/MM/dd HH:mm:ss"));
                        } else {
                            cell.setCellType(1);// 将所有字段默认为字符串类型，避免某些数字类型，由于长度太长导致变为科学计数
                            if (judgeDateFormat(cell.toString().trim())) {// 如果是格式为“yyyy-MM-dd HH:mm:ss”的日期文本，将“-”替换为“/”
                                rowList.add(cell.toString().trim().replace("-", "/"));// 将当前行的每一列保存到rowList
                            } else {
                                rowList.add(cell.toString().trim());// 将当前行的每一列保存到rowList
                            }
                        }
                    }
                }

                list.add(rowList);// 将每一行保存到list
            }
        }

        return list;
    }

    /**
     * 导入xls
     *
     * @param in 导入的xls读取流
     * @return list
     * @author liufeidong
     */
    public static List<List<String>> importXls(InputStream in) {
        List<List<String>> list = new ArrayList<List<String>>();
        HSSFWorkbook book = null;
        try {
            book = new HSSFWorkbook(in);

            // 循环xlsx每一页
            for (int sheetNum = 0; sheetNum < book.getNumberOfSheets(); sheetNum++) {
                HSSFSheet sheet = book.getSheetAt(sheetNum);
                if (sheet == null) {
                    continue;
                }
                HSSFRow headRow = sheet.getRow(0);// 表头
                if (headRow == null) {
                    continue;
                }
                int minColIx = headRow.getFirstCellNum();// 获取第一列序号
                int maxColIx = headRow.getLastCellNum();// 获取最后一列序号
                // 循环当页的每一行
                for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {// 从下标“1”开始，跳过表头
                    HSSFRow row = sheet.getRow(rowNum);// 获取当前行
                    if (row == null) {
                        continue;
                    }
                    List<String> rowList = new ArrayList<String>();// 定义rowList

                    // 循环每行的每一列
                    for (int colIx = minColIx; colIx < maxColIx; colIx++) {
                        HSSFCell cell = row.getCell(colIx);// 获取当前列

                        if (cell == null) {
                            rowList.add("");// 将当前行的每一列保存到rowList
                        } else {
                            if (cell.getCellType() == cell.CELL_TYPE_NUMERIC && HSSFDateUtil.isCellDateFormatted(cell)) {// 日期类型，转格式
                                rowList.add(DateUtil.formatStrByDate(cell.getDateCellValue(), "yyyy/MM/dd HH:mm:ss"));
                            } else {
                                cell.setCellType(1);// 将所有字段默认为字符串类型，避免某些数字类型，由于长度太长导致变为科学计数
                                // (经过测试这种方式会将科学计数法1.03E+08转为"1.03E+08"字符串保存到list中，并非数字的字符串。
                                // 新加了一个方法，importXlsxByType(),remark by hepingJiang)
                                if (judgeDateFormat(cell.toString().trim())) {// 如果是格式为“yyyy-MM-dd HH:mm:ss”的日期文本，将“-”替换为“/”
                                    rowList.add(cell.toString().trim().replace("-", "/"));// 将当前行的每一列保存到rowList
                                } else {
                                    rowList.add(cell.toString().trim());// 将当前行的每一列保存到rowList
                                }
                            }
                        }
                    }

                    list.add(rowList);// 将每一行保存到list
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 导入xlsx
     *
     * @param in 导入的xlsx读取流,sheetNbr为0表示第一个sheet表依次类推。为空则默认查询所有的sheet表
     * @return list
     * @author huangyiying
     */
    public static List<List<String>> importSheetXlsx(InputStream in, Integer sheetNbr) {
        List<List<String>> list = new ArrayList<List<String>>();
        XSSFWorkbook book = null;
        try {
            book = new XSSFWorkbook(in);
            if (sheetNbr != null) {
                XSSFSheet sheet = book.getSheetAt(sheetNbr);
                if (sheet != null) {
                    XSSFRow headRow = sheet.getRow(0);// 表头
                    if (headRow != null) {
                        int minColIx = headRow.getFirstCellNum();// 获取第一列序号
                        int maxColIx = headRow.getLastCellNum();// 获取最后一列序号
                        // 循环当页的每一行
                        for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {// 从下标“1”开始，跳过表头
                            XSSFRow row = sheet.getRow(rowNum);// 获取当前行
                            if (row == null) {
                                continue;
                            }

                            List<String> rowList = new ArrayList<String>();// 定义rowList

                            // 循环每行的每一列
                            for (int colIx = minColIx; colIx < maxColIx; colIx++) {
                                XSSFCell cell = row.getCell(colIx);// 获取当前列

                                if (cell == null) {
                                    rowList.add("");// 将当前行的每一列保存到rowList
                                } else {
                                    if (cell.getCellType() == cell.CELL_TYPE_NUMERIC && HSSFDateUtil.isCellDateFormatted(cell)) {// 日期类型，转格式
                                        rowList.add(DateUtil.formatStrByDate(cell.getDateCellValue(), "yyyy/MM/dd HH:mm:ss"));
                                    } else {
                                        cell.setCellType(1);// 将所有字段默认为字符串类型，避免某些数字类型，由于长度太长导致变为科学计数
                                        if (judgeDateFormat(cell.toString().trim())) {// 如果是格式为“yyyy-MM-dd HH:mm:ss”的日期文本，将“-”替换为“/”
                                            rowList.add(cell.toString().trim().replace("-", "/"));// 将当前行的每一列保存到rowList
                                        } else {
                                            rowList.add(cell.toString().trim());// 将当前行的每一列保存到rowList
                                        }
                                    }
                                }
                            }

                            list.add(rowList);// 将每一行保存到list
                        }
                    }
                }
            } else {
                // 循环xlsx每一页
                for (XSSFSheet sheet : book) {
                    if (sheet == null) {
                        continue;
                    }
                    XSSFRow headRow = sheet.getRow(0);// 表头
                    if (headRow == null) {
                        continue;
                    }
                    int minColIx = headRow.getFirstCellNum();// 获取第一列序号
                    int maxColIx = headRow.getLastCellNum();// 获取最后一列序号
                    // 循环当页的每一行
                    for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {// 从下标“1”开始，跳过表头
                        XSSFRow row = sheet.getRow(rowNum);// 获取当前行
                        if (row == null) {
                            continue;
                        }

                        List<String> rowList = new ArrayList<String>();// 定义rowList

                        // 循环每行的每一列
                        for (int colIx = minColIx; colIx < maxColIx; colIx++) {
                            XSSFCell cell = row.getCell(colIx);// 获取当前列

                            if (cell == null) {
                                rowList.add("");// 将当前行的每一列保存到rowList
                            } else {
                                if (cell.getCellType() == cell.CELL_TYPE_NUMERIC && HSSFDateUtil.isCellDateFormatted(cell)) {// 日期类型，转格式
                                    rowList.add(DateUtil.formatStrByDate(cell.getDateCellValue(), "yyyy/MM/dd HH:mm:ss"));
                                } else {
                                    cell.setCellType(1);// 将所有字段默认为字符串类型，避免某些数字类型，由于长度太长导致变为科学计数
                                    if (judgeDateFormat(cell.toString().trim())) {// 如果是格式为“yyyy-MM-dd HH:mm:ss”的日期文本，将“-”替换为“/”
                                        rowList.add(cell.toString().trim().replace("-", "/"));// 将当前行的每一列保存到rowList
                                    } else {
                                        rowList.add(cell.toString().trim());// 将当前行的每一列保存到rowList
                                    }
                                }
                            }
                        }

                        list.add(rowList);// 将每一行保存到list
                    }
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 导入或取指定sheet表xls
     *
     * @param in 导入的xls读取流,sheetNbr值为0代表第一个sheet表，依次类推。为null则是所有sheet表
     * @return list
     * @author huangyiying
     */
    public static List<List<String>> importSheetXls(InputStream in, Integer sheetNbr) {
        List<List<String>> list = new ArrayList<List<String>>();
        HSSFWorkbook book = null;
        try {
            book = new HSSFWorkbook(in);
            Integer flag = 1;
            // 循环xlsx每一页
            for (int sheetNum = 0; sheetNum < book.getNumberOfSheets() && flag == 1; sheetNum++) {
                HSSFSheet sheet = book.getSheetAt(sheetNum);
                if (sheetNbr != null) {
                    sheet = book.getSheetAt(sheetNbr);
                }
                if (sheet == null) {
                    continue;
                }
                HSSFRow headRow = sheet.getRow(0);// 表头
                if (headRow == null) {
                    continue;
                }
                int minColIx = headRow.getFirstCellNum();// 获取第一列序号
                int maxColIx = headRow.getLastCellNum();// 获取最后一列序号
                // 循环当页的每一行
                for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {// 从下标“1”开始，跳过表头
                    HSSFRow row = sheet.getRow(rowNum);// 获取当前行
                    if (row == null) {
                        continue;
                    }
                    List<String> rowList = new ArrayList<String>();// 定义rowList

                    // 循环每行的每一列
                    for (int colIx = minColIx; colIx < maxColIx; colIx++) {
                        HSSFCell cell = row.getCell(colIx);// 获取当前列

                        if (cell == null) {
                            rowList.add("");// 将当前行的每一列保存到rowList
                        } else {
                            if (cell.getCellType() == cell.CELL_TYPE_NUMERIC && HSSFDateUtil.isCellDateFormatted(cell)) {// 日期类型，转格式
                                rowList.add(DateUtil.formatStrByDate(cell.getDateCellValue(), "yyyy/MM/dd HH:mm:ss"));
                            } else {
                                cell.setCellType(1);// 将所有字段默认为字符串类型，避免某些数字类型，由于长度太长导致变为科学计数
                                if (judgeDateFormat(cell.toString().trim())) {// 如果是格式为“yyyy-MM-dd HH:mm:ss”的日期文本，将“-”替换为“/”
                                    rowList.add(cell.toString().trim().replace("-", "/"));// 将当前行的每一列保存到rowList
                                } else {
                                    rowList.add(cell.toString().trim());// 将当前行的每一列保存到rowList
                                }
                            }
                        }
                    }

                    list.add(rowList);// 将每一行保存到list
                    if (sheetNbr != null) {
                        flag = 0;
                    }
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return list;
    }

    private static boolean judgeDateFormat(String date) {
        String eL = "[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}";
        Pattern p = Pattern.compile(eL);
        Matcher m = p.matcher(date);
        return m.matches();
    }

    public static int getExcelRowCount(InputStream in, String type) {
        int rowCount = 0;
        try {
            if ("xlsx".equals(type.toLowerCase())) {// xlsx格式
                XSSFWorkbook book = new XSSFWorkbook(in);

                // 循环xlsx每一页
                for (XSSFSheet sheet : book) {
                    if (sheet == null) {
                        continue;
                    }
                    rowCount = rowCount + sheet.getLastRowNum();
                }
            } else if ("xls".equals(type.toLowerCase())) {// xls格式
                HSSFWorkbook book = new HSSFWorkbook(in);

                // 循环xlsx每一页
                for (int sheetNum = 0; sheetNum < book.getNumberOfSheets(); sheetNum++) {
                    HSSFSheet sheet = book.getSheetAt(sheetNum);
                    if (sheet == null) {
                        continue;
                    }
                    rowCount = rowCount + sheet.getLastRowNum();
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return rowCount;
    }

    public static int getExcelSheetRowCount(InputStream in, String type, Integer sheetNbr) {
        int rowCount = 0;
        try {
            if ("xlsx".equals(type.toLowerCase())) {// xlsx格式
                XSSFWorkbook book = new XSSFWorkbook(in);
                if (sheetNbr != null) {
                    XSSFSheet sheet = book.getSheetAt(sheetNbr);
                    if (sheet != null) {
                        rowCount = rowCount + sheet.getLastRowNum();
                    }
                } else {
                    // 循环xlsx每一页
                    for (XSSFSheet sheet : book) {
                        if (sheet == null) {
                            continue;
                        }
                        rowCount = rowCount + sheet.getLastRowNum();
                    }
                }
            } else if ("xls".equals(type.toLowerCase())) {// xls格式
                HSSFWorkbook book = new HSSFWorkbook(in);
                if (sheetNbr != null) {
                    HSSFSheet sheet = book.getSheetAt(sheetNbr);
                    if (sheet != null) {
                        rowCount = rowCount + sheet.getLastRowNum();
                    }
                } else {
                    // 循环xlsx每一页
                    for (int sheetNum = 0; sheetNum < book.getNumberOfSheets(); sheetNum++) {
                        HSSFSheet sheet = book.getSheetAt(sheetNum);
                        if (sheet == null) {
                            continue;
                        }
                        rowCount = rowCount + sheet.getLastRowNum();
                    }
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return rowCount;
    }

    /**
     * xlsx数据类型
     *
     * @param cell
     * @return String
     * 获取单元格中的值
     * @author: JiangHePing
     */
    static private String getXCellVal(XSSFCell cell) throws Exception {
        String val = "";
        SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd"); //日期格式yyyy-mm-dd
        DecimalFormat df = new DecimalFormat("0.##");             //数字格式，防止长数字成为科学计数法形式，或者int变为double形式
        switch (cell.getCellType()) {
            case XSSFCell.CELL_TYPE_NUMERIC:

                if (HSSFDateUtil.isCellDateFormatted(cell)) {//excel上的日期格式为*2012年12月10日，否则判断不成功
                    val = fmt.format(cell.getDateCellValue()); //日期型
//					val = (Integer.parseInt(val)-70*365-19)*86400-8*3600+"";
                } else {
                    val = df.format(cell.getNumericCellValue()); //数字型
//					val = (Integer.parseInt(val)-70*365-19)*86400-8*3600+"";
                }

//				try{
//					long l = Long.valueOf(val);
//					val = fmt.format(new Date(l * 1000L));
//				}catch (Exception e){
//					e.printStackTrace();
//				}
                break;
            case XSSFCell.CELL_TYPE_STRING: //文本类型
                val = cell.getStringCellValue();
                break;
            case XSSFCell.CELL_TYPE_BOOLEAN: //布尔型
                val = String.valueOf(cell.getBooleanCellValue());
                break;
            case XSSFCell.CELL_TYPE_BLANK: //空白
                val = cell.getStringCellValue();
                break;
            case XSSFCell.CELL_TYPE_ERROR: //错误
                val = "错误";
                break;
            case XSSFCell.CELL_TYPE_FORMULA: //公式
                try {
                    val = String.valueOf(cell.getNumericCellValue());
                } catch (IllegalStateException e) {
                    val = String.valueOf(cell.getRichStringCellValue());
                }
                break;
            default:
                val = cell.getRichStringCellValue() == null ? null : cell.getRichStringCellValue().toString();
        }
        return val;
    }

    /**
     * xls数据类型
     *
     * @param cell
     * @return String
     * 获取单元格中的值
     * @author: JiangHePing
     */
    static private String getXCellVal(HSSFCell cell) throws Exception {
        String val = "";
        SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd"); //日期格式yyyy-mm-dd
        DecimalFormat df = new DecimalFormat("0.##");             //数字格式，防止长数字成为科学计数法形式，或者int变为double形式
        switch (cell.getCellType()) {
            case XSSFCell.CELL_TYPE_NUMERIC:
                /*try {
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
					val = fmt.format(cell.getDateCellValue()); //日期型
				} else {
					val = df.format(cell.getNumericCellValue()); //数字型
				}

					  val = (Integer.parseInt(val) - 70 * 365 - 19) * 86400 - 8 * 3600 + "";
					  }catch(Exception e){*/
                cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                val = cell.getStringCellValue();
                break;
            //  }
                /*try{
					long l = Long.valueOf(val);
					val = fmt.format(new Date(l * 1000L));
				}catch (Exception e){
					e.printStackTrace();
				}
				break;*/
            case XSSFCell.CELL_TYPE_STRING: //文本类型
                val = cell.getStringCellValue();
                break;
            case XSSFCell.CELL_TYPE_BOOLEAN: //布尔型
                val = String.valueOf(cell.getBooleanCellValue());
                break;
            case XSSFCell.CELL_TYPE_BLANK: //空白
                val = cell.getStringCellValue();
                break;
            case XSSFCell.CELL_TYPE_ERROR: //错误
                val = "错误";
                break;
            case XSSFCell.CELL_TYPE_FORMULA: //公式
                try {
                    val = String.valueOf(cell.getNumericCellValue());
                } catch (IllegalStateException e) {
                    val = String.valueOf(cell.getRichStringCellValue());
                }
                break;
            default:
                val = cell.getRichStringCellValue() == null ? null : cell.getRichStringCellValue().toString();
        }
        return val;
    }

    /**
     * 导入xlsx
     *
     * @param in 导入的xlsx读取流
     * @return list
     * @author JiangHePing
     */
    public static List<List<String>> importXlsxByType(InputStream in) throws Exception {
        List<List<String>> list = new ArrayList<List<String>>();
        XSSFWorkbook book = null;
        book = new XSSFWorkbook(in);

        // 循环xlsx每一页
        for (XSSFSheet sheet : book) {
            if (sheet == null) {
                continue;
            }
            XSSFRow headRow = sheet.getRow(0);// 表头
            if (headRow == null) {
                continue;
            }
            int minColIx = headRow.getFirstCellNum();// 获取第一列序号
            int maxColIx = headRow.getLastCellNum();// 获取最后一列序号
            // 循环当页的每一行
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {// 从下标“1”开始，跳过表头
                XSSFRow row = sheet.getRow(rowNum);// 获取当前行
                if (row == null) {
                    continue;
                }

                List<String> rowList = new ArrayList<String>();// 定义rowList

                // 循环每行的每一列
                for (int colIx = minColIx; colIx < maxColIx; colIx++) {
                    XSSFCell cell = row.getCell(colIx);// 获取当前列

                    if (cell == null) {
                        rowList.add("");// 将当前行的每一列保存到rowList
                    } else {
                        rowList.add(getXCellVal(cell));// 将当前行的每一列保存到rowList
                    }
                }

                list.add(rowList);// 将每一行保存到list
            }
        }

        return list;
    }

    /**
     * 导入xlsx
     *
     * @param in   导入的xlsx读取流
     * @param jump 跳过几多行（从表头下标0开始计算，跳过jump行）
     * @return list
     * @author JiangHePing
     */
    public static List<List<String>> importAutoXlsxByType(InputStream in, int jump) throws Exception {
        List<List<String>> list = new ArrayList<List<String>>();
        XSSFWorkbook book = null;
        book = new XSSFWorkbook(in);

        // 循环xlsx每一页
        for (XSSFSheet sheet : book) {
            if (sheet == null) {
                continue;
            }
            XSSFRow headRow = sheet.getRow(0);// 表头
            if (headRow == null) {
                continue;
            }
            int minColIx = headRow.getFirstCellNum();// 获取第一列序号
            int maxColIx = headRow.getLastCellNum();// 获取最后一列序号
            // 循环当页的每一行
            for (int rowNum = jump; rowNum <= sheet.getLastRowNum(); rowNum++) {// 从下标“1”开始，跳过表头
                XSSFRow row = sheet.getRow(rowNum);// 获取当前行
                if (row == null) {
                    continue;
                }

                List<String> rowList = new ArrayList<String>();// 定义rowList

                // 循环每行的每一列
                for (int colIx = minColIx; colIx < maxColIx; colIx++) {
                    XSSFCell cell = row.getCell(colIx);// 获取当前列

                    if (cell == null) {
                        rowList.add("");// 将当前行的每一列保存到rowList
                    } else {
                        rowList.add(getXCellVal(cell));// 将当前行的每一列保存到rowList
                    }
                }

                list.add(rowList);// 将每一行保存到list
            }
        }

        return list;
    }

    /**
     * 导入xls
     *
     * @param in 导入的xls读取流
     * @return list
     * @author JiangHePing
     */
    public static List<List<String>> importXlsByType(InputStream in) throws Exception {
        List<List<String>> list = new ArrayList<List<String>>();
        HSSFWorkbook book = null;
        book = new HSSFWorkbook(in);

        // 循环xlsx每一页
        for (int sheetNum = 0; sheetNum < book.getNumberOfSheets(); sheetNum++) {
            HSSFSheet sheet = book.getSheetAt(sheetNum);
            if (sheet == null) {
                continue;
            }
            HSSFRow headRow = sheet.getRow(0);// 表头
            if (headRow == null) {
                continue;
            }
            int minColIx = headRow.getFirstCellNum();// 获取第一列序号
            int maxColIx = headRow.getLastCellNum();// 获取最后一列序号
            // 循环当页的每一行
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {// 从下标“1”开始，跳过表头
                HSSFRow row = sheet.getRow(rowNum);// 获取当前行
                if (row == null) {
                    continue;
                }
                List<String> rowList = new ArrayList<String>();// 定义rowList

                // 循环每行的每一列
                for (int colIx = minColIx; colIx < maxColIx; colIx++) {
                    HSSFCell cell = row.getCell(colIx);// 获取当前列

                    if (cell == null) {
                        rowList.add("");// 将当前行的每一列保存到rowList
                    } else {
                        rowList.add(getXCellVal(cell));
                    }
                }

                list.add(rowList);// 将每一行保存到list
            }
        }
        return list;
    }

    /**
     * 导入xls
     *
     * @param in   导入的xls读取流
     * @param jump （从表头下标0开始计算，跳过jump行）
     * @return list
     * @author JiangHePing
     */
    public static List<List<String>> importAutoXlsByType(InputStream in, int jump) throws Exception {
        List<List<String>> list = new ArrayList<List<String>>();
        HSSFWorkbook book = null;
        book = new HSSFWorkbook(in);

        // 循环xlsx每一页
        for (int sheetNum = 0; sheetNum < book.getNumberOfSheets(); sheetNum++) {
            HSSFSheet sheet = book.getSheetAt(sheetNum);
            if (sheet == null) {
                continue;
            }
            HSSFRow headRow = sheet.getRow(0);// 表头
            if (headRow == null) {
                continue;
            }
            int minColIx = headRow.getFirstCellNum();// 获取第一列序号
            int maxColIx = headRow.getLastCellNum();// 获取最后一列序号
            // 循环当页的每一行
            for (int rowNum = jump; rowNum <= sheet.getLastRowNum(); rowNum++) {// 从下标“1”开始，跳过表头
                HSSFRow row = sheet.getRow(rowNum);// 获取当前行
                if (row == null) {
                    continue;
                }
                List<String> rowList = new ArrayList<String>();// 定义rowList

                // 循环每行的每一列
                for (int colIx = minColIx; colIx < maxColIx; colIx++) {
                    HSSFCell cell = row.getCell(colIx);// 获取当前列

                    if (cell == null) {
                        rowList.add("");// 将当前行的每一列保存到rowList
                    } else {
                        rowList.add(getXCellVal(cell));
                    }
                }

                list.add(rowList);// 将每一行保存到list
            }
        }

        return list;
    }

    public static void main(String[] args) throws Exception {
        File file = new File("D:\\取消订单\\养老金上门取消订单.xlsx");
        InputStream in = null;
        try {
            in = new FileInputStream(file);
        } catch (Exception e) {

        }
        List<List<String>> list = new ArrayList<List<String>>();
        list = IOUtil.importXlsxByType(in);// 导入xlsx格式
        for (int i = 0; i < list.size(); i++) {
            System.out.println(list.get(i).get(0) + "," + list.get(i).get(1));
        }
    }
}
